go

exec dbo.p_drop_proc_if_exists 'p_create_enum_constraint';

go

--Function of adding enum values constraint
create procedure p_create_enum_constraint(
       --Column id
       @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Enum value list
    @a_enum_value_list varchar(max),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_enum_value_list = '';
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name
    from meta_enum_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --Enum value cursor
    declare @a_value_cursor cursor,
    --Enum value
    @a_enum_value numeric;
    
    set @a_value_cursor = cursor fast_forward read_only for
    select value from meta_enum_members
    where column_id = @a_column_id;
    
    open @a_value_cursor;
    fetch next from @a_value_cursor into @a_enum_value;
    
    --Searching enum values
    while @@fetch_status = 0
    begin         
        if @a_enum_value_list <> ''
            set @a_enum_value_list = @a_enum_value_list + ' or ';
                     
        set @a_enum_value_list = @a_enum_value_list + @a_column_name + '=' + cast(@a_enum_value as varchar);
        
        fetch next from @a_value_cursor into @a_enum_value;    
    end;
    
    close @a_value_cursor;
    deallocate @a_value_cursor;
         
    if @a_enum_value_list <> ''     
        --Adding enum column constraint
        exec ('alter table tbl_' + @a_table_name
        + ' add constraint m' + @a_column_id_str
        + ' check(' + @a_enum_value_list + ')');
    else
		--(so, one should specify enum members before inserting)
		exec ('alter table tbl_' + @a_table_name
         + ' add constraint m' + @a_column_id_str
         + ' check(' + @a_column_name + ' is null)');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_enum_constraint';

go

--Function af dropping enum values constraint
create procedure p_drop_enum_constraint(
       --Column id
       @a_column_id binary(16))
as
begin
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);

    --Reading column properties
    select @a_table_id = table_id
    from meta_enum_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
     
    exec ('alter table tbl_' + @a_table_name
    + ' drop constraint m' + @a_column_id_str);     
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_enum_column';

go

--Function of creating enum column
create procedure meta_add_enum_column(
    --Table id
    @a_table_id binary(16),
    --Column id
    @a_column_id binary(16),
    --Column name
    @a_column_name varchar(max))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_col_not_exists @a_table_id, @a_column_id, @a_column_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name =  dbo.p_get_table_name(@a_table_id);
    
    --Changing the metadata
    insert into meta_enum_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (@a_column_id, @a_table_id, @a_column_name,
        dbo.p_generate_order_index(@a_table_id), 1);

    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_table_name;
         
    exec ('alter table tbl_' 
    + @a_table_name
    + ' add ' + @a_column_name
    + ' numeric(10)');
    
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
         
    --Creating enum values constraint
    exec dbo.p_create_enum_constraint @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_enum_member';

go

--Function of adding new enum value
create procedure meta_add_enum_member(
       --Column id
       @a_column_id binary(16),
       --Value
       @a_value numeric(10))
with execute as owner
as
begin
	set nocount on;
	declare
	--Row count
	@a_row_count numeric,
	--Column id str
	@a_column_id_str varchar(32);
	
	set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Searching for enum member
    select @a_row_count = count(*)
    from meta_enum_members
    where column_id = @a_column_id and value = @a_value;
    
    if @a_row_count <> 0
    begin
		declare @a_value_str varchar(10);
		set @a_value_str = cast(@a_value as varchar);
        raiserror('A member with the specified value was already added (column_id = %s, value = %s) (#error_code = 20054)',
        16, 1, @a_column_id_str, @a_value_str);
        return;
    end;
    
    set xact_abort on;
    begin tran;

    --Changing the metadata
    insert into meta_enum_members (column_id, value)
    values(@a_column_id, @a_value);

    --Dropping enum constraint
    exec dbo.p_drop_enum_constraint @a_column_id;
         
    --Creating new enum constraint
    exec dbo.p_create_enum_constraint @a_column_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_enum_member';

go

--Function of removing enum value
create procedure meta_delete_enum_member(
    --Column id
    @a_column_id binary(16),
    --Value
    @a_value numeric(10))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Default value
    @a_default_value numeric(10),
    --Row count
    @a_row_count numeric,
    --Column id str
    @a_column_id_str varchar(32);
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Searching for enum member
    select @a_row_count = count(*)
    from meta_enum_members
    where column_id = @a_column_id and value = @a_value;
    
    if @a_row_count = 0
    begin
        raiserror('Deleting nonexistent enumeration member is forbidden (column_id = %s) (#error_code = 20018)',
        16, 1, @a_column_id_str);        
        return;
    end;

    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_default_value = default_value
    from meta_enum_columns
    where column_id = @a_column_id;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If default value was deleted
    if @a_value = @a_default_value
        --Setting default value to null
        exec dbo.meta_set_enum_default @a_column_id, null;
    
    --Changing the metadata
    delete from meta_enum_members
    where column_id = @a_column_id
    and value = @a_value;
    
	--Dropping enum constraint
	exec dbo.p_drop_enum_constraint @a_column_id;
	     
	--Setting deleted member values to null
	exec ('update tbl_' + @a_table_name
	+ ' set ' + @a_column_name
	+ ' = null'
	+ ' where ' + @a_column_name + ' = ' + @a_value);

	--Creating new enum constraint
	exec dbo.p_create_enum_constraint @a_column_id;

	commit;      
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_enum_member_value';

go

--Function of changing enum member value
create procedure meta_set_enum_member_value(
    --Column name
    @a_column_id binary(16),
    --Old value
    @a_old_value numeric(10),
    --New value
    @a_new_value numeric(10))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Default value
    @a_default_value numeric(10);

    if @a_new_value = @a_old_value
        return;
        
    set xact_abort on;
    begin tran;
        
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_default_value = default_value
    from meta_enum_columns
    where column_id = @a_column_id;
        
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    --If default value is equal to old value
    if @a_default_value = @a_old_value
        --Setting default value to null
        exec dbo.meta_set_enum_default @a_column_id, null;
    
    --Changing the metadata
    update meta_enum_members
    set value = @a_new_value
    where column_id = @a_column_id and value = @a_old_value;
        
    --Dropping old enum contraing
    exec dbo.p_drop_enum_constraint @a_column_id;
                 
    --Updating old member values
    exec ('update tbl_' + @a_table_name
    + ' set ' + @a_column_name
    + ' = ' + @a_new_value
    + ' where ' + @a_column_name
    + ' = ' + @a_old_value);
         
    --Creating new enum constraint
    exec dbo.p_create_enum_constraint @a_column_id;
    
    --If default value is equal to old value
    if @a_default_value = @a_old_value
        --Setting default value to new value
        exec dbo.meta_set_enum_default @a_column_id, @a_new_value;
    
    commit;     
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_enum_default';

go

--Function of setting enum column default value
create procedure meta_set_enum_default(
    --Column id
    @a_column_id binary(16),
    --Default value
    @a_default_value numeric(10))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Column name
    @a_column_name varchar(26),
    --Result
    @a_result numeric(1),
    --Column id str
    @a_column_id_str varchar(32),
    --Old default value
    @a_old_default_value numeric(10);
    
    exec dbo.p_check_col_type @a_column_id, 'meta_enum_columns', 'enumeration', @a_result output;
    
    if @a_result = 0 return;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    --Reading column properties
    select @a_table_id = table_id, @a_column_name = column_name,
    @a_old_default_value = default_value
    from meta_enum_columns
    where column_id = @a_column_id;
    
    --If there is no need for change
    if (@a_old_default_value = @a_default_value) or
    (@a_old_default_value is null and @a_default_value is null)
		return;
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);
    
    set xact_abort on;
    begin tran;
    
    --If there is an old default value
    if @a_old_default_value is not null
		--Dropping the old default value
		exec ('alter table tbl_' 
        + @a_table_name
        + ' drop constraint def_' + @a_column_id_str);
    
    --Updating the metadata
    update meta_enum_columns
    set default_value = @a_default_value
    where column_id = @a_column_id;
    
    --If there is a new default value
    if @a_default_value is not null
        exec ('alter table tbl_' 
        + @a_table_name
        + ' add constraint def_' + @a_column_id_str
        + ' default ' + @a_default_value
        + ' for ' + @a_column_name);        
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_enum_col_constrs';

go

--Function of creating all enum constraints
create procedure p_create_enum_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	exec dbo.p_create_enum_constraint @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_enum_col_constrs';

go

--Function of dropping all enum constraints
create procedure p_drop_enum_col_constrs(
	--Column id
	@a_column_id binary(16))
as
begin
	exec dbo.p_drop_enum_constraint @a_column_id;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_enum_value_checking';

go

--Function of enabling/disabling enum value checking
create procedure meta_set_enum_value_checking(
    --The column id
    @a_column_id binary(16),
    --The need for enum value checking
    @a_value_checking numeric(1))
with execute as owner
as
begin
    set nocount on;
	declare
	--Result
	@a_result numeric(1),
	--Column id str
    @a_column_id_str varchar(32),
	--Table name
    @a_table_name varchar(26);

    exec dbo.p_check_col_type @a_column_id, 'meta_enum_columns', 'enumeration', @a_result output;
    
    if @a_result = 0 return;
    
    --Reading the table name
    select @a_table_name = table_name
    from meta_table_columns
    where column_id = @a_column_id;
    
    set @a_column_id_str = dbo.p_bin_to_str(@a_column_id);
    
    set xact_abort on;
    begin tran;
    
    --If value checking is enabled
    if @a_value_checking = 1
        exec ('alter table tbl_' + @a_table_name
        + ' with check check constraint m' + @a_column_id_str);
    --If value checking is disabled
    else
        exec ('alter table tbl_' + @a_table_name
        + ' with check nocheck constraint m' + @a_column_id_str);
    
    --Updating the metadata
    update meta_enum_columns
    set value_checking = @a_value_checking
    where column_id = @a_column_id;
    
    commit;
end;

go